package ca.llsutherland.nfl.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import ca.llsutherland.nfl.domain.Division;
import ca.llsutherland.nfl.domain.Team;
import ca.llsutherland.nfl.exception.ValidationException;
import ca.llsutherland.nfl.helper.TeamHelper;
import ca.llsutherland.nfl.util.StringUtil;

public class TeamDao extends BaseDao {

	private static TeamDao instance = new TeamDao();

    @Override
    public String getUnderlyingTableName() {
        return "TEAM";
    }

    public static TeamDao getInstance() {
        return instance ;
    }

    public Team findByName(String name) {
        String findByNameQuery = getFindByNameQuery(name);
        return buildTeam(findByNameQuery);
    }
    

	public Team findByCity(String city) {
		String sql = "SELECT * FROM TEAM WHERE CITY = " + StringUtil.addSingleQuotes(city) + ";";
		return buildTeam(sql);
	}

    private Team buildTeam(String sql) {
        Team team = null;
        ResultSet rs = super.findByQuery(sql);
        
        try {
            if(rs.next()){
            	Long id = rs.getLong("ID");
            	String city = rs.getString("CITY");
            	String nickname = rs.getString("NICKNAME");
            	String shortCity = rs.getString("SHORT_CITY");
            	String shortNickname = rs.getString("SHORT_NICKNAME");
            	Long divisionId = rs.getLong("DIVISION_ID");
            	Division division = DivisionDao.getInstance().findById(divisionId);
            	team = TeamHelper.createTeam(id, city, nickname, shortCity, shortNickname, division);
            }
            rs.close();
        } catch (SQLException e) {
            throw new ValidationException(e.getMessage() + " in buildTeam()");
        } finally {
            rs = null;            
        }
        return team;
    }

    public Team create(Team team) {
        String sql = "INSERT INTO " + getUnderlyingTableName() + " (CITY, NICKNAME, SHORT_CITY, SHORT_NICKNAME, DIVISION_ID) VALUES (" 
        + StringUtil.addSingleQuotes(team.getCity()) + ","
        + StringUtil.addSingleQuotes(team.getNickname()) + "," 
        + StringUtil.addSingleQuotes(team.getShortCity()) + "," 
        + StringUtil.addSingleQuotes(team.getShortNickname()) + "," 
        + team.getDivision().getId() + ");";
        return findById(super.create(sql));
    }

    public Team findById(Long id) {
        String findByIdQuery = getFindByIdQuery(id);
        return buildTeam(findByIdQuery);
    }

}
